package com.example.demo;

import com.alibaba.excel.EasyExcel;
import com.google.common.base.Splitter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author linjingze
 * @date 2023/12/15 8:22 下午
 */
public class ExcelReader {


    public static List<String> SUPPORT_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            "宁波纽约专线不包税-宁波",
            "宁波纽约专线包税-宁波",
            "宁波休斯顿专线不包税-宁波",
            "宁波休斯顿专线包税-宁波",
            "宁波芝加哥专线包税-宁波",
            "宁波芝加哥专线不包税-宁波"
    );

    public static List<String> UN_NB_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            "青岛美东海外仓卡派-不包税",
            "上海美东卡派包税",
            "休斯顿卡派(包税)",
            "芝加哥卡派(包税)",
            "纽约卡派(包税)"
    );

    public static List<String> NYC_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            "宁波纽约专线不包税-宁波",
            "宁波纽约专线包税-宁波"
    );
    public static List<String> CHICAGO_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            "宁波芝加哥专线包税-宁波",
            "宁波芝加哥专线不包税-宁波"
    );
    public static List<String> HOU_INBOUND_CHANNEL_NAME_LIST = Arrays.asList(
            "宁波休斯顿专线不包税-宁波",
            "宁波休斯顿专线包税-宁波"
    );




   static String FBA_PATTERN = "^[a-zA-Z]{3}\\d$";

   static Pattern FBA_P = Pattern.compile(FBA_PATTERN);


    public static void reader(File file) {
        List<CargoData> dataList = EasyExcel.read(file).head(CargoData.class).sheet().doReadSync();

        //1.创建一个工作薄
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

        XSSFCellStyle cellStyle = xssfWorkbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        XSSFFont font = xssfWorkbook.createFont();
        // 字体大小:字号
        font.setFontHeightInPoints((short) 8);
        font.setFontName("微软雅黑");
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        String opFileName = file.getParent() + File.separator + "周报" + System.currentTimeMillis() + ".xlsx";
        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(opFileName);
            w1(xssfWorkbook, cellStyle, dataList);
            w2(xssfWorkbook, cellStyle, dataList);
            w3(xssfWorkbook, cellStyle, dataList);
            w4(xssfWorkbook, cellStyle, dataList);
            w5(xssfWorkbook, cellStyle, dataList);
            w6(xssfWorkbook, cellStyle, dataList);
            xssfWorkbook.write(fileOutputStream);
            OpenUtils.open(opFileName);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            IOUtils.closeQuietly(fileOutputStream);
        }

    }

    public static void main(String[] args) throws IOException {
        String fileName = "/Users/linjingze/Downloads/12.xlsx";

    }


    public static void w1(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("美东自拼TOP10客户货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData("美东自拼TOP10客户货量统计表", 1, 2));
        rowDataList.add(new BaseRowData("客户名称", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 3).setStartRow(2));

        List<CustomerRowData> customerRowDataList = getCustomerRowData(dataList);
        int c = 3;
        for (int i = 0; i < Math.min(customerRowDataList.size(), 10); i++) {
            CustomerRowData customerRowData = customerRowDataList.get(i);
            rowDataList.add(new BaseRowData(customerRowData.getCustomerName(),1).setStartRow(c));
            rowDataList.add(new BaseRowData(customerRowData.getSales(),2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(customerRowData.getVolume()), 3).setStartRow(c));
            c++;
        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    private static List<CustomerRowData> getCustomerRowData(List<CargoData> dataList) {
        return dataList.stream().filter(s -> SUPPORT_INBOUND_CHANNEL_NAME_LIST.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(s -> s.getCustomerName() + "," + s.getSales(), Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())))
                .entrySet().stream().map(e -> {
                    List<String> keyList = Splitter.on(",").splitToList(e.getKey());
                    CustomerRowData customerRowData = new CustomerRowData(keyList.get(0), keyList.get(1), e.getValue());
                    return customerRowData;
                }).sorted().collect(Collectors.toList());
    }

    public static void w2(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("美线各渠道货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData("美线各渠道货量统计表", 1, 3));
        rowDataList.add(new BaseRowData("入库渠道", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("总计（CBM）", 4).setStartRow(2));

        Map<String, BigDecimal> map = dataList.stream().filter(s -> SUPPORT_INBOUND_CHANNEL_NAME_LIST.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(CargoData::getInboundChannelName, Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())));

        int c = 3;
        rowDataList.add(new BaseRowData("", 1).setMergeRow(SUPPORT_INBOUND_CHANNEL_NAME_LIST.size() - 1).setStartRow(c));

        BigDecimal sum = BigDecimal.ZERO;
        for (Map.Entry<String, BigDecimal> e : map.entrySet()) {
            BigDecimal value = e.getValue();
            sum = BigDecimalUtils.add(value, sum);
            String key = e.getKey();
            BaseRowData r = new BaseRowData(key, 2).setStartRow(c);
            rowDataList.add(r);
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(value), 3).setStartRow(c));
            c++;
        }

        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 4).setMergeRow(SUPPORT_INBOUND_CHANNEL_NAME_LIST.size() - 1).setStartRow(3));
        rowDataList.add(new BaseRowData("总计", 1).setStartRow(3 + SUPPORT_INBOUND_CHANNEL_NAME_LIST.size()));;
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 2).setStartRow(3 + SUPPORT_INBOUND_CHANNEL_NAME_LIST.size()));

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static void w3(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波销售走非宁波渠道货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData("宁波销售走非宁波渠道货量统计表", 1, 5));
        rowDataList.add(new BaseRowData("入库渠道", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("业务员", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("客户名称", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("求和项:入库体积", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("总计", 6).setStartRow(2));

        Map<String, List<CargoData>> map = dataList.stream().filter(s -> UN_NB_INBOUND_CHANNEL_NAME_LIST.contains(s.getInboundChannelName()))
                .collect(Collectors.groupingBy(CargoData::getInboundChannelName));

        int c = 3;
        BigDecimal sum = BigDecimal.ZERO;
        for (Map.Entry<String, List<CargoData>> e : map.entrySet()) {
            List<CargoData> cargoDataList = e.getValue();
            List<CustomerRowData> customerRowDataList = convert(cargoDataList);
            BigDecimal value = customerRowDataList.stream().map(CustomerRowData::getVolume).collect(BigDecimalCollector.toSum());
            sum = BigDecimalUtils.add(value, sum);
            String key = e.getKey();
            rowDataList.add(new BaseRowData(key, 2).setMergeRow(customerRowDataList.size() - 1).setStartRow(c));
            for (CustomerRowData cargoData : customerRowDataList) {
                rowDataList.add(new BaseRowData(cargoData.getSales(), 3).setStartRow(c));
                rowDataList.add(new BaseRowData(cargoData.getCustomerName(), 4).setStartRow(c));
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(cargoData.getVolume()), 5).setStartRow(c));
                c++;
            }
        }
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 6).setMergeRow(c - 3 - 1).setStartRow(3));
        rowDataList.add(new BaseRowData("总计", 1).setStartRow(c));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 4).setStartRow(c));

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static List<CustomerRowData> convert(List<CargoData> cargoDataList) {
        return cargoDataList.stream()
                .collect(Collectors.groupingBy(s -> s.getCustomerName() + "," + s.getSales(), Collectors.mapping(CargoData::getVolume, BigDecimalCollector.toSum())))
                .entrySet().stream().map(e -> {
                    List<String> keyList = Splitter.on(",").splitToList(e.getKey());
                    CustomerRowData customerRowData = new CustomerRowData(keyList.get(0), keyList.get(1), e.getValue());
                    return customerRowData;
                }).sorted().collect(Collectors.toList());
    }

    public static void w4(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波美东自拼-销售各仓货量统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData("宁波美东自拼 入库数据销售各仓货量统计表", 1, 5));
        rowDataList.add(new BaseRowData("销售", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("FBA", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("沃尔玛", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("私人地址", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("总计", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("总货量占比", 6).setStartRow(2));

        Map<String, List<WarehouseData>> map = dataList.stream()
                .filter(c -> {
                    String dept = c.getDept();
                    return dept.contains("宁波分公司销售") || dept.contains("销售中心");
                }).map(c -> {
                    WarehouseData warehouseData = convert(c);
                    return warehouseData;

                }).collect(Collectors.groupingBy(WarehouseData::getSaleDept));

        int c = 3;
        BigDecimal fbaSum = BigDecimal.ZERO;
        BigDecimal wrmSum = BigDecimal.ZERO;
        BigDecimal srdzSum = BigDecimal.ZERO;
        BigDecimal sum = map.values().stream().flatMap(Collection::stream)
                .map(WarehouseData::getWeight).collect(BigDecimalCollector.toSum());
        if (!BigDecimalUtils.eqZero(sum)) {
            for (Map.Entry<String, List<WarehouseData>> e : map.entrySet()) {
                List<WarehouseData> cargoDataList = e.getValue();
                Map<String, BigDecimal> wMap =
                        cargoDataList.stream().filter(s -> !StringUtils.isBlank(s.getWarehouse()))
                                .collect(Collectors.groupingBy(WarehouseData::getWarehouse,
                                Collectors.mapping(WarehouseData::getWeight, BigDecimalCollector.toSum())));

                String key = e.getKey();
                rowDataList.add(new BaseRowData(key, 1).setStartRow(c));
                BaseRowData fba = new BaseRowData("//", 2).setStartRow(c);
                rowDataList.add(fba);
                BigDecimal amount = BigDecimal.ZERO;
                if (wMap.containsKey("FBA")) {
                    BigDecimal fba1 = wMap.get("FBA");
                    fba.setName(BigDecimalUtils.toPlainString(fba1));
                    amount = BigDecimalUtils.add(amount, fba1);
                    fbaSum = BigDecimalUtils.add(fbaSum, fba1);
                }
                BaseRowData wrm = new BaseRowData("//", 3).setStartRow(c);
                rowDataList.add(wrm);
                if (wMap.containsKey("WRM")) {
                    BigDecimal wrm1 = wMap.get("WRM");
                    wrm.setName(BigDecimalUtils.toPlainString(wrm1));
                    amount = BigDecimalUtils.add(amount, wrm1);
                    wrmSum = BigDecimalUtils.add(wrm1, wrmSum);
                }
                BaseRowData srdz = new BaseRowData("//", 4).setStartRow(c);
                rowDataList.add(srdz);
                if (wMap.containsKey("SRDZ")) {
                    BigDecimal srdz1 = wMap.get("SRDZ");
                    srdz.setName(BigDecimalUtils.toPlainString(srdz1));
                    amount = BigDecimalUtils.add(amount, srdz1);
                    srdzSum = BigDecimalUtils.add(srdz1, srdzSum);
                }
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(amount), 5).setStartRow(c));

                BigDecimal bigDecimal = BigDecimalUtils.divKeep8Scale(amount, sum);
                bigDecimal = BigDecimalUtils.mul(bigDecimal, new BigDecimal("100"));
                rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(bigDecimal) + "%", 6).setStartRow(c));
                c++;
            }
            rowDataList.add(new BaseRowData("总计", 1).setStartRow(c));

            BaseRowData fbaAmount = new BaseRowData("//", 2).setStartRow(c);
            if (!BigDecimalUtils.eqZero(fbaSum)) {
                fbaAmount.setName(BigDecimalUtils.toPlainString(fbaSum));
            }
            BaseRowData wrmAmount = new BaseRowData("//", 3).setStartRow(c);
            if (!BigDecimalUtils.eqZero(wrmSum)) {
                wrmAmount.setName(BigDecimalUtils.toPlainString(wrmSum));
            }
            BaseRowData srdzAmount = new BaseRowData("//", 4).setStartRow(c);
            if (!BigDecimalUtils.eqZero(srdzSum)) {
                srdzAmount.setName(BigDecimalUtils.toPlainString(srdzSum));
            }
            BaseRowData allAmount = new BaseRowData("//", 5).setStartRow(c);
            if (!BigDecimalUtils.eqZero(sum)) {
                allAmount.setName(BigDecimalUtils.toPlainString(sum));
            }
            rowDataList.addAll(Arrays.asList(fbaAmount, wrmAmount, srdzAmount, allAmount));
        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    private static WarehouseData convert(CargoData c) {
        WarehouseData warehouseData = new WarehouseData();
        String dept = c.getDept();
        if (dept.contains("宁波分公司销售")) {
            warehouseData.setSaleDept("宁波销售");
        } else {
            warehouseData.setSaleDept("深圳销售+渠道");
        }
        String warehouse = c.getWarehouse();
        if (StringUtils.isBlank(warehouse)) {
            warehouseData.setWarehouse("SRDZ");
        }
         else if ( FBA_P.matcher(warehouse).matches()) {
            warehouseData.setWarehouse("FBA");
        } else if (warehouse.contains("沃尔玛")) {
            warehouseData.setWarehouse("WRM");
        }
        warehouseData.setWeight(c.getActualWeight());
        return warehouseData;
    }

    public static void w5(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波美东自拼-各目的港数据表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData("自拼入库各目的港数据表", 1, 5));
        rowDataList.add(new BaseRowData("目的港", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("FBA", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("沃尔玛仓库", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("私人地址", 4).setStartRow(2));

        Map<String, List<WarehouseData>> map = dataList.stream()
                .filter(c -> SUPPORT_INBOUND_CHANNEL_NAME_LIST.contains(c.getOutboundChannelName()))
                .map(c -> {
                    WarehouseData warehouseData = convert(c);
                    String outboundChannelName = c.getOutboundChannelName();
                    if (NYC_INBOUND_CHANNEL_NAME_LIST.contains(outboundChannelName)) {
                        warehouseData.setDestPort("NYC(NEW YORK)");
                    } else if(CHICAGO_INBOUND_CHANNEL_NAME_LIST.contains(outboundChannelName)) {
                        warehouseData.setDestPort("CHICAGO(CHICAGO)");
                    } else if (HOU_INBOUND_CHANNEL_NAME_LIST.contains(outboundChannelName)) {
                        warehouseData.setDestPort("HOU(HOUSTON)");
                    }
                    return warehouseData;

                }).collect(Collectors.groupingBy(WarehouseData::getDestPort));

        int c = 3;
        BigDecimal fbaSum = BigDecimal.ZERO;
        BigDecimal wrmSum = BigDecimal.ZERO;
        BigDecimal srdzSum = BigDecimal.ZERO;
        BigDecimal sum = map.values().stream().flatMap(Collection::stream)
                .map(WarehouseData::getWeight).collect(BigDecimalCollector.toSum());
        if (!BigDecimalUtils.eqZero(sum)) {
            for (Map.Entry<String, List<WarehouseData>> e : map.entrySet()) {
                List<WarehouseData> cargoDataList = e.getValue();
                Map<String, BigDecimal> wMap =
                        cargoDataList.stream().filter(s -> !StringUtils.isBlank(s.getWarehouse()))
                                .collect(Collectors.groupingBy(WarehouseData::getWarehouse,
                                        Collectors.mapping(WarehouseData::getWeight, BigDecimalCollector.toSum())));

                String key = e.getKey();
                rowDataList.add(new BaseRowData(key, 1).setStartRow(c));
                BaseRowData fba = new BaseRowData("//", 2).setStartRow(c);
                rowDataList.add(fba);
                if (wMap.containsKey("FBA")) {
                    BigDecimal fba1 = wMap.get("FBA");
                    fba.setName(BigDecimalUtils.toPlainString(fba1));
                    fbaSum = BigDecimalUtils.add(fbaSum, fba1);
                }
                BaseRowData wrm = new BaseRowData("//", 3).setStartRow(c);
                rowDataList.add(wrm);
                if (wMap.containsKey("WRM")) {
                    BigDecimal wrm1 = wMap.get("WRM");
                    wrm.setName(BigDecimalUtils.toPlainString(wrm1));
                    wrmSum = BigDecimalUtils.add(wrm1, wrmSum);
                }
                BaseRowData srdz = new BaseRowData("//", 4).setStartRow(c);
                rowDataList.add(srdz);
                if (wMap.containsKey("SRDZ")) {
                    BigDecimal srdz1 = wMap.get("SRDZ");
                    srdz.setName(BigDecimalUtils.toPlainString(srdz1));
                    srdzSum = BigDecimalUtils.add(srdz1, srdzSum);
                }
                c++;
            }
            rowDataList.add(new BaseRowData("求和", 1).setStartRow(c));

            BaseRowData fbaAmount = new BaseRowData("//", 2).setStartRow(c);
            if (!BigDecimalUtils.eqZero(fbaSum)) {
                fbaAmount.setName(BigDecimalUtils.toPlainString(fbaSum));
            }
            BaseRowData wrmAmount = new BaseRowData("//", 3).setStartRow(c);
            if (!BigDecimalUtils.eqZero(wrmSum)) {
                wrmAmount.setName(BigDecimalUtils.toPlainString(wrmSum));
            }
            BaseRowData srdzAmount = new BaseRowData("//", 4).setStartRow(c);
            if (!BigDecimalUtils.eqZero(srdzSum)) {
                srdzAmount.setName(BigDecimalUtils.toPlainString(srdzSum));
            }
            rowDataList.addAll(Arrays.asList(fbaAmount, wrmAmount, srdzAmount));

            rowDataList.add(new BaseRowData("总计", 1).setStartRow(c+1));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(sum), 2, 2).setStartRow(c + 1));

        }
        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }

    public static void w6(XSSFWorkbook xssfWorkbook, XSSFCellStyle cellStyle, List<CargoData> dataList) {
        //2.创建工作表
        XSSFSheet sheet = xssfWorkbook.createSheet("宁波美东渠道平均计费重统计表");

        List<BaseRowData> rowDataList = new ArrayList<>();
        rowDataList.add(new BaseRowData("宁波美东渠道平均计费重统计表", 1, 5));
        rowDataList.add(new BaseRowData("", 1).setStartRow(2));
        rowDataList.add(new BaseRowData("柜量40HQ", 2).setStartRow(2));
        rowDataList.add(new BaseRowData("实际体积", 3).setStartRow(2));
        rowDataList.add(new BaseRowData("实重", 4).setStartRow(2));
        rowDataList.add(new BaseRowData("计费体积", 5).setStartRow(2));
        rowDataList.add(new BaseRowData("计费重", 6).setStartRow(2));

        Map<String, List<DestportData>> map = dataList.stream()
                .filter(c -> SUPPORT_INBOUND_CHANNEL_NAME_LIST.contains(c.getOutboundChannelName()))
                .map(c -> {
                    DestportData warehouseData = new DestportData();
                    String outboundChannelName = c.getOutboundChannelName();
                    if (NYC_INBOUND_CHANNEL_NAME_LIST.contains(outboundChannelName)) {
                        warehouseData.setDestPort("NYC拆");
                    } else if(CHICAGO_INBOUND_CHANNEL_NAME_LIST.contains(outboundChannelName)) {
                        warehouseData.setDestPort("CHI拆");
                    } else if (HOU_INBOUND_CHANNEL_NAME_LIST.contains(outboundChannelName)) {
                        warehouseData.setDestPort("HOU拆");
                    }
                    warehouseData.setTeu(c.getTeu());
                    BigDecimal actualVolume = c.getVolume();
                    warehouseData.setActualVolume(actualVolume);
                    BigDecimal actualWeight = c.getActualWeight();
                    warehouseData.setActualWeight(actualWeight);

                    warehouseData.setWeight(BigDecimalUtils.max(BigDecimalUtils.mul(actualVolume, new BigDecimal("167")),actualWeight));
                    warehouseData.setVolume(BigDecimalUtils.max(BigDecimalUtils.div(actualWeight, new BigDecimal("363")), actualVolume));

                    warehouseData.setTeu(c.getTeu());

                    return warehouseData;

                }).collect(Collectors.groupingBy(DestportData::getDestPort));

        int c = 3;
        BigDecimal teuSum = BigDecimal.ZERO;
        BigDecimal actualWeightSum = BigDecimal.ZERO;
        BigDecimal actualVolumeSum = BigDecimal.ZERO;
        BigDecimal volumeSum = BigDecimal.ZERO;
        BigDecimal weightSum = BigDecimal.ZERO;

        for (Map.Entry<String, List<DestportData>> e : map.entrySet()) {
            List<DestportData> cargoDataList = e.getValue();
            String key = e.getKey();
            rowDataList.add(new BaseRowData(key, 1).setStartRow(c));

            BigDecimal teuAmount = BigDecimal.ZERO;
            BigDecimal actualVolumeAmount = BigDecimal.ZERO;
            BigDecimal actualWeightAmount = BigDecimal.ZERO;
            BigDecimal volumeAmount = BigDecimal.ZERO;
            BigDecimal weightAmount = BigDecimal.ZERO;
            for (DestportData destportData : cargoDataList) {
                teuAmount = BigDecimalUtils.add(teuAmount, BigDecimalUtils.toBigDecimal(destportData.getTeu()));
                actualVolumeAmount = BigDecimalUtils.add(actualVolumeAmount, destportData.getActualVolume());
                actualWeightAmount = BigDecimalUtils.add(actualWeightAmount, destportData.getActualWeight());
                volumeAmount = BigDecimalUtils.add(volumeAmount, destportData.getVolume());
                weightAmount = BigDecimalUtils.add(weightAmount, destportData.getWeight());
            }
            teuSum = BigDecimalUtils.add(teuSum, teuAmount);
            actualWeightSum = BigDecimalUtils.add(actualWeightSum, actualWeightAmount);
            actualVolumeSum = BigDecimalUtils.add(actualVolumeSum, actualVolumeAmount);
            volumeSum = BigDecimalUtils.add(volumeSum, volumeAmount);
            weightSum = BigDecimalUtils.add(weightSum, weightAmount);

            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(teuAmount), 2).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(actualVolumeAmount), 3).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(actualWeightAmount), 4).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(volumeAmount), 5).setStartRow(c));
            rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(weightAmount), 6).setStartRow(c));

            c++;
        }
        rowDataList.add(new BaseRowData("平均值", 1).setStartRow(c));
        BigDecimal size = new BigDecimal(map.keySet().size());
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(teuSum, size)), 2).setStartRow(c));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(actualVolumeSum, size)), 3).setStartRow(c));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(actualWeightSum, size)), 4).setStartRow(c));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(volumeSum, size)), 5).setStartRow(c));
        rowDataList.add(new BaseRowData(BigDecimalUtils.toPlainString(BigDecimalUtils.div(weightSum, size)), 6).setStartRow(c));

        ExcelUtils.writeRow(sheet, cellStyle, rowDataList);
    }
}
